Improving the Design of a Database
If you're going to develop a database, you might at well make it look good. By doing this, you can improve the databases ability to provide information and to enhance the operational characteristics. It's important to understand that Normalization cannot be relied on to make designs by itself but will need to be created separately. The following six concepts are just a few ideas to help boost a databases design. Evaluate Primary Key Assignments When you're adding specific entities to a database, it's important to understand the overall concept of how these entities are affecting the rest of the database. You will need to focus on specific entities, usually the Primary Keys, in order to make a successful and reliable database. Inputting information can be very repetitive when doing it consistently, making it more prone for errors leading to referential integrity violations. A surrogate key could be used in this instance by creating tables through primary keys. A surrogate key is generated though the DBMS and is usually hidden from the end-users. A SK is usually numeric as well. Evaluate Naming Conventions The purpose of revising naming conventions is to provide a more accurate set of details or to help split up a broad entity/attribute into more of these categories. Lets say you have a broad category such Car_Parts but no specifics onto what the car parts do. Adding a Parts_Description entity would do numerous wonders to describing each attribute more clearly. Refine Attribute Atomicity An atomic attribute is a category to which it can no longer be divided into smaller categories. However having an attribute displaying atomicity is can be used in the same group but revised enough to get specific information. Using the example of User_Address; the address is composed of various bits of information such as the specific address itself, the city, state, and zip code. You can easily add atomicity to this example by displaying User_StAddress, User_City, User_State, and User_Zip all in separate areas. Identify New Attributes When creating attributes that affect one another, it's important to understand the links they may have to other unknown attributes. A good example would be based off a Participant table in which each Participant may have categories such as the amount of surveys, any payments and specific identities. By adding a new participant attribute, you can track the processes each participant goes through and when they start these processes. Identify New Relationships One of the main goals of a database is to reduce the number of undesired data duplications. It is important as a designer to lace the right attributesin the right tables through the normalization process. Refine Primary Keys as Required for Data Granularity Obviously data that is created in the lower levels of the database (Atomic) and sub-levels are not as important as the higher, broader attributes. However using the granularity of these low key atomic values will help keep the database more detailed and specific on what needs to be categorized where. An example of this could involve assigning the number of hours an individual drives. However this attribute isn't clear enough by forgetting to state the specifics of the time periods the individual drives such as daily, weekly, monthly and so on. Be sure to focus on explaining these details clearly so if you're arranging a report you can sort by more specific details. The page was summarized from the following: Rob, P., & Coronel, C. (2009). Database Systems: Design, Implementation, and Management. Boston, MA, US: Thomson: Course Technology. P. 164-167